﻿using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static NPOI.POIFS.Crypt.CryptoFunctions;

namespace CloudApiServer.Services
{
    /// <summary>
    /// 签到签退服务
    /// </summary>
    public class SignInSignOutService
    {
        /// <summary>
        /// 9001 服务签到
        /// </summary>
        /// <param name="fsiRequest">签到入参</param>
        /// <param name="CountrollerName">来源控制器名称</param>
        /// <returns>签到返参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<outputsignInOut> SignInAsync(FsiRequestHeader<InputSignIn> fsiRequest, string CountrollerName)
        {
            string fixmedinsCode = fsiRequest.fixmedins_code;
            string fixmedinsName = fsiRequest.fixmedins_name;
            string ip = fsiRequest.Input.signIn.ip;
            string mac = fsiRequest.Input.signIn.mac;
            string opterNo = fsiRequest.Input.signIn.opter_no;

            if (string.IsNullOrEmpty(fixmedinsCode) || string.IsNullOrEmpty(fixmedinsName) || string.IsNullOrEmpty(ip) || string.IsNullOrEmpty(mac) || string.IsNullOrEmpty(opterNo))
            {
                throw new Exception("参数校验失败：传入的机构代码|机构名称|IP|mac|操作员编号为空，请检查入参！");
            }
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";

            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                var rnd = new Random();
                string signNo = rnd.Next(1000000, 10000000).ToString();
                string signTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO fsi_signinout VALUES (null, @fixmedins_code, @fixmedins_name, @ip, @mac, @opter_no, @sign_no, 0, @signinTime, NULL)";
                    cmd.Parameters.AddWithValue("@fixmedins_code", fixmedinsCode);
                    cmd.Parameters.AddWithValue("@fixmedins_name", fixmedinsName);
                    cmd.Parameters.AddWithValue("@ip", ip);
                    cmd.Parameters.AddWithValue("@mac", mac);
                    cmd.Parameters.AddWithValue("@opter_no", opterNo);
                    cmd.Parameters.AddWithValue("@sign_no", signNo);
                    cmd.Parameters.AddWithValue("@signinTime", signTime);

                    await cmd.ExecuteNonQueryAsync();
                }
                var output = new outputsignInOut()
                {
                    signinoutb = new signInOut()
                    {
                        sign_no = signNo,
                        sign_time = signTime
                    }
                };
                return output;
            }
        }
        /// <summary>
        /// 9001 服务签到 山东进销存
        /// </summary>
        /// <param name="fsiRequest">签到入参</param>
        /// <param name="CountrollerName">来源控制器名称</param>
        /// <returns>签到返参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<outputsignInOutShandong> SignInShandongAsync(FsiRequestHeader<InputSignInShandong> fsiRequest, string CountrollerName)
        {
            string fixmedinsCode = fsiRequest.fixmedins_code;
            string fixmedinsName = fsiRequest.fixmedins_name;
            string ip = fsiRequest.Input.data.ip;
            string mac = fsiRequest.Input.data.mac;
            string opterNo = fsiRequest.Input.data.opter_no;

            if (string.IsNullOrEmpty(fixmedinsCode) || string.IsNullOrEmpty(fixmedinsName) || string.IsNullOrEmpty(ip) || string.IsNullOrEmpty(mac) || string.IsNullOrEmpty(opterNo))
            {
                throw new Exception("参数校验失败：传入的机构代码|机构名称|IP|mac|操作员编号为空，请检查入参！");
            }
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";

            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                var rnd = new Random();
                string signNo = rnd.Next(1000000, 10000000).ToString();
                string signTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO fsi_signinout VALUES (null, @fixmedins_code, @fixmedins_name, @ip, @mac, @opter_no, @sign_no, 0, @signinTime, NULL)";
                    cmd.Parameters.AddWithValue("@fixmedins_code", fixmedinsCode);
                    cmd.Parameters.AddWithValue("@fixmedins_name", fixmedinsName);
                    cmd.Parameters.AddWithValue("@ip", ip);
                    cmd.Parameters.AddWithValue("@mac", mac);
                    cmd.Parameters.AddWithValue("@opter_no", opterNo);
                    cmd.Parameters.AddWithValue("@sign_no", signNo);
                    cmd.Parameters.AddWithValue("@signinTime", signTime);

                    await cmd.ExecuteNonQueryAsync();
                }
                var output = new outputsignInOutShandong()
                {
                    result = new signInOut()
                    {
                        sign_no = signNo,
                        sign_time = signTime
                    }
                };
                return output;
            }
        }

        /// <summary>
        /// 9002 服务签退
        /// </summary>
        /// <param name="fsiRequest">签退入参</param>
        /// <param name="CountrollerName">来源控制器名称</param>
        /// <returns>签退返参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<outputsignInOut> SignOutAsync(FsiRequestHeader<InputSignOut> fsiRequest,string CountrollerName)
        {
            string fixmedins_code = fsiRequest.fixmedins_code;
            string opter_no = fsiRequest.Input.signOut.opter_no;
            string sign_no = fsiRequest.Input.signOut.sign_no;
            if (string.IsNullOrEmpty(fixmedins_code) || string.IsNullOrEmpty(sign_no) || string.IsNullOrEmpty(opter_no))
            {
                throw new Exception("参数校验失败：传入的机构代码|签到号|操作员编号为空，请检查入参！");
            }
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                DateTime dateTime = DateTime.Now;
                string siginoutTime = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from fsi_signinout where fixmedins_code=@fixmedins_code and opter_no=@opter_no and sign_no= @sign_no and state=0 ";
                    cmd.Parameters.AddWithValue("@fixmedins_code", fixmedins_code);
                    cmd.Parameters.AddWithValue("@opter_no", opter_no);
                    cmd.Parameters.AddWithValue("@sign_no", sign_no);
                    using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        if (dt.Rows.Count < 1)
                        {
                            throw new Exception($"查询签到表失败：根据传入的机构代码、操作员编号和签到编号未找到签到信息或已是签退状态！当前获取到了{dt.Rows.Count}条有效的签到记录。");
                        }
                        else
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = "update  fsi_signinout set state=1,siginoutTime=@siginoutTime where fixmedins_code=@fixmedins_code and opter_no=@opter_no and sign_no= @sign_no and state=0 ";
                            cmd.Parameters.AddWithValue("@fixmedins_code", fixmedins_code);
                            cmd.Parameters.AddWithValue("@opter_no", opter_no);
                            cmd.Parameters.AddWithValue("@sign_no", sign_no);
                            cmd.Parameters.AddWithValue("@siginoutTime", siginoutTime);
                            await cmd.ExecuteNonQueryAsync();
                            var output = new outputsignInOut()
                            {
                                signinoutb = new signInOut()
                                {
                                    sign_no = sign_no,
                                    sign_time = siginoutTime
                                }
                            };
                            return output;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 9002 服务签退 山东进销存
        /// </summary>
        /// <param name="fsiRequest">签退入参</param>
        /// <param name="CountrollerName">来源控制器名称</param>
        /// <returns>签退返参</returns>
        /// <exception cref="Exception"></exception>
        public async Task<outputsignInOutShandong> SignOutShandongAsync(FsiRequestHeader<InputSignOutShandong> fsiRequest,string CountrollerName)
        {
            string fixmedins_code = fsiRequest.fixmedins_code;
            string opter_no = fsiRequest.Input.data.opter_no;
            string sign_no = fsiRequest.Input.data.sign_no;
            if (string.IsNullOrEmpty(fixmedins_code) || string.IsNullOrEmpty(sign_no) || string.IsNullOrEmpty(opter_no))
            {
                throw new Exception("参数校验失败：传入的机构代码|签到号|操作员编号为空，请检查入参！");
            }
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                DateTime dateTime = DateTime.Now;
                string siginoutTime = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from fsi_signinout where fixmedins_code=@fixmedins_code and opter_no=@opter_no and sign_no= @sign_no and state=0 ";
                    cmd.Parameters.AddWithValue("@fixmedins_code", fixmedins_code);
                    cmd.Parameters.AddWithValue("@opter_no", opter_no);
                    cmd.Parameters.AddWithValue("@sign_no", sign_no);
                    using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        if (dt.Rows.Count < 1)
                        {
                            throw new Exception($"查询签到表失败：根据传入的机构代码、操作员编号和签到编号未找到签到信息或已是签退状态！当前获取到了{dt.Rows.Count}条有效的签到记录。");
                        }
                        else
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = "update  fsi_signinout set state=1,siginoutTime=@siginoutTime where fixmedins_code=@fixmedins_code and opter_no=@opter_no and sign_no= @sign_no and state=0 ";
                            cmd.Parameters.AddWithValue("@fixmedins_code", fixmedins_code);
                            cmd.Parameters.AddWithValue("@opter_no", opter_no);
                            cmd.Parameters.AddWithValue("@sign_no", sign_no);
                            cmd.Parameters.AddWithValue("@siginoutTime", siginoutTime);
                            await cmd.ExecuteNonQueryAsync();
                            var output = new outputsignInOutShandong()
                            {
                                result = new signInOut()
                                {
                                    sign_no = sign_no,
                                    sign_time = siginoutTime
                                }
                            };
                            return output;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 校验签到信息
        /// </summary>
        /// <param name="fixmedinscode">机构代码</param>
        /// <param name="opter_no">操作员编号</param>
        /// <param name="signno">签到值</param>
        /// <returns>是否已签到</returns>
        /// <exception cref="Exception"></exception>
        public async Task<bool> SignInStateAsync(string fixmedinscode, string opter_no, string signno)
        {
            if (string.IsNullOrEmpty(fixmedinscode) || string.IsNullOrEmpty(opter_no) || string.IsNullOrEmpty(signno))
            {
                throw new Exception($"校验签到信息失败：传入的签到号【{signno}】|操作员编号【{opter_no}】|机构代码【{fixmedinscode}】不能为空！");
            }

            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from fsi_signinout where fixmedins_code=@fixmedins_code and opter_no=@opter_no and sign_no= @sign_no and state=0 ";
                    cmd.Parameters.AddWithValue("@fixmedins_code", fixmedinscode);
                    cmd.Parameters.AddWithValue("@opter_no", opter_no);
                    cmd.Parameters.AddWithValue("@sign_no", signno);
                    using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        if (dt.Rows.Count < 1)
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                }
            }
        }
    }
}
